package mysql

import (
	"database/sql"
	"fmt"

	"go.uber.org/zap"

	"github.com/spf13/viper"

	_ "github.com/go-sql-driver/mysql" //init()
)

var db *sql.DB

//初始化数据库连接
func InitDB() (err error) {
	dsn := fmt.Sprintf("%s:%s@tcp(%s:%d)/%s?charset=utf8mb4&parseTime=True",
		viper.GetString("mysql.user"),
		viper.GetString("mysql.password"),
		viper.GetString("mysql.host"),
		viper.GetInt("mysql.port"),
		viper.GetString("mysql.dbname"),
	)
	db, err = sql.Open("mysql", dsn)
	if err != nil {
		zap.L().Error("connect DN error ", zap.Error(err))
		return
	}

	err = db.Ping()
	if err != nil {
		fmt.Printf("connect failed,err:%v\n", err)
		return
	}
	db.SetMaxOpenConns(viper.GetInt("mysql.max_open_conns")) //最大连接数
	db.SetMaxIdleConns(viper.GetInt("max_idle_conns"))       //最大空闲连接
	return nil
}

type user struct {
	id   int
	name string
	age  int
}

//预处理查询--场景为批量操作，可以提前编译节约耗时，又可避免sql注入
func prepareQuery() {
	sqlstr := "select * from user where id>?"
	stmt, err := db.Prepare(sqlstr)
	if err != nil {
		fmt.Println("error", err)
		return
	}
	defer stmt.Close()
	rows, err := stmt.Query(0)
	if err != nil {
		return
	}
	defer rows.Close()
	//循环读取结果集数据
	for rows.Next() {
		var u user
		err := rows.Scan(&u.id, &u.name, &u.age)
		if err != nil {
			fmt.Println("scan failed")
			return
		}
		fmt.Printf("id:%d,name:%s,age:%d\n", u.id, u.name, u.age)
	}

}

//事务操作实例
func transaction() {
	tx, err := db.Begin()
	if err != nil {
		if tx != nil {
			tx.Rollback()
		}
		fmt.Println("begin 失败")
		return
	}
	sqlstr1 := "update  user set age=30 where id=?"
	ret1, err := tx.Exec(sqlstr1, 2)
	if err != nil {
		tx.Rollback()
		fmt.Println("exec sql1 error", err)
		return
	}
	affrow1, err := ret1.RowsAffected()
	if err != nil {
		tx.Rollback()
		fmt.Println("ret1.RowsAffected() error", err)
		return
	}
	sqlstr2 := "update  user set age=50 where id=?"
	ret2, err := tx.Exec(sqlstr2, 3)
	if err != nil {
		tx.Rollback()
		fmt.Println("exec sql2 error", err)
		return
	}
	affrow2, err := ret2.RowsAffected()
	if err != nil {
		tx.Rollback()
		fmt.Println("ret2.RowsAffected() error", err)
		return
	}
	//当两条提交均成功时提交数据
	if affrow1 == 1 && affrow2 == 1 {
		err = tx.Commit() //提交事务
		if err != nil {
			tx.Rollback()
			fmt.Println("commit error", err)
			return
		}
		fmt.Println("事务提交了")
	} else {
		tx.Rollback()
		fmt.Println("exec rollback")
	}

	fmt.Println("执行成功")
}

func Close() {
	_ = db.Close()
}
func main1() {
	err := InitDB() //调用函数
	if err != nil {
		fmt.Printf("init db failed,err:%v\n", err)
		return
	}
	//释放数据库连接资源
	defer db.Close()
	fmt.Println("success")
	prepareQuery()
	transaction()

}
